<?php
/*
 * Copyright (c) Taras Kudla, 2010
 */

# last checking: 15.03.2010, status:worked
class pgsql extends kernel implements dbostruct{ 
	# connection descriptor
	private $connection=null;
	# database name
	private $database = null;
	
	# basic pgsql object contstruct
	public function init(){
		if(!extension_loaded("pgsql")){
			$this->core->error("DBO_PGSQL","Cannot found PGSQL PHP module");
		}
	}
	
	function __destruct(){
	
	}

	public function connect($serv,$port,$db,$user,$passwd){
		$connectline = "host=".$serv." dbname=".$db.(($port!=null)?" port=".$port:null)." user=".$user." password=".$passwd;

		if($this->core->get("dbopersistent")){
			$c=@pg_pconnect($connectline);
		}else {
			$c=@pg_connect($connectline);
		}
		if(! is_resource($c)){
			$this->core->error("DBO_DRIVER_CONNECT","CANNOT CONNECT TO THE `PGSQL` DATABASE SERVER. CHECK PARAMETERS PLEASE.");
		}else{
			$this->connection=&$c;
		}
		$this->database=$db;
	}

	public function disconnect(){
		if(is_resource($this->connection)) {
			pg_close($this->connection);
		}
	}

	# main sql select method with result handling
	public function select($table, $fields = "*", $sql = null, $type = _RESULT_ASSOC){
		$sql = "select " . $fields . " from " . $table . " " . $sql;
		$q = @pg_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_SELECT", (is_resource($this->connection) ? pg_last_error($this->connection) : null));
			return false;
		}
		switch ($type){
            case _RESULT_ASSOC:
                return $this->sqlToAssoc($q);
                break;
            case _RESULT_LIST:
                return $this->sqlToList($q, $fields);
                break;
            case _RESULT_BOOL:
                return ((bool) pg_num_rows($q));
                break;
            case _RESULT_PAIRS:
                return $this->sqlToPairs($q);
                break;
            case _RESULT_NUMROW:
                return $this->sqlToNumRow($q);
                break;		
            case _RESULT_ROW:
                return $this->sqlToRow($q);
                break;
            case _RESULT_SINGLE:
	    	case _RESULT_ONE:
	    		return $this->sqlToSingle($q);
	    		break;	    
            default:
                return $this->sqlToAssoc($q);
        }
	}
	
	# sql result to simple array ( as list )
	private function &sqlToList(&$q, $fields){
		if (preg_match("/[,]/", $fields, $null)){
			$this->core->error("DBO_DRIVER_SELECT", "Error in results ordering as LIST. Field should be without comas.");
		return false;
		}
		$a = array();
		while($row=pg_fetch_row($q)){
			$a[] = $row[0];
		}
		return $a;
	}


	private function sqlToSingle(&$q){
    	if( pg_num_rows($q) == 1 && pg_num_fields($q)== 1 ){
    		$q=pg_fetch_row($q);
    		return $q[0];
    	}   
    	return null; 
    }

    # sql select result as associative array ( like dictionary in python )
	private function sqlToRow(&$q){
		if (pg_num_rows($q) == 0)
			return array();
		return pg_fetch_assoc($q);
	}

	# sql select result as numeric indexes array
	private function sqlToNumRow(&$q){
		if (pg_num_rows($q) == 0)
			return array();
		return pg_fetch_row($q);
	}

	# sql select result to array of pairs key=>value
	private function &sqlToPairs(&$q){
		$array = array();
		if (pg_num_fields($q) != 2){
			$this->core->error("DBO", "Cannot build pairs for result. Fields number must be 2");
		return false;
		}
		while ($row = pg_fetch_row($q)){
			$array[$row[0]] = $row[1];
		}
		return $array;
	}

	# sql select result to arrays of associative array
	private function &sqlToAssoc(&$q){
		$array = array();
		$num = pg_num_rows($q);
		for ($i = 0; $i < $num; $i++){
			$array[$i] = pg_fetch_assoc($q);
		}
		return $array;
	}

	# inserting records to sql table
	public function insert($table, array $data,$returnid=null){
		if (!is_array($data) || count($data) < 1){
			return false;
		}
		$sql = "INSERT INTO $table (".implode(",",array_keys($data)).") VALUES ('".implode("','",array_values($data))."')";
		$q = @pg_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_INSERT", pg_last_error($this->connection));
		return false;
		}
		if( $returnid!=null){
			# returning last id
			$query = $this->select($table,"$returnid","",_RESULT_ONE);
			return $query;
		}
		return true;
	}

	# updating table
	public function update($table, $sql){
		$sql = "update " . $table . " " . $sql;
		$q = @pg_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_UPDATE", pg_last_error($this->connection));
		return false;
		}
		return true;
	}

    # deleting from table  
	public function delete($table, $sql){
		$sql = "delete from " . $table . " " . $sql;
		$q = @pg_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_DELETE", pg_last_error($this->connection));
		}
		return $q;
	}

	# direct sql query
	public function execute($sql){
		$q = @pg_query($this->connection,$sql);
		if ($q == false){
			$this->core->error("DBO_DRIVER_DIRECT", pg_last_error($this->connection));
			return false;
		}
		return $q;
	}
	
	public function version(){
		$data=pg_version($this->connection);
		return $data["server"];
	}
	
	public function droptable($name){
		$this->execute("DROP TABLE IF EXISTS $name");
	}
	
	public function createtable(sqltable $map){
	
     	list($name,$fields,$keys,$encoding,$engine) = $map->get();    	
      	$pkeys =array();
      	foreach( $keys as $k=>$v ){
      		if( $v[0]==_KEY_PRIMARY) $pkeys[]=$v[1];
      	}
      	$text = "CREATE TABLE $name ( ";
      	# fields processing      	
      	foreach( $fields as $k=>$v){
      		list($fname,$ftype,$fsize,$fdefault,$fnull) = $v;
      		$fresult = $this->types($ftype,$fsize);
      		if( $ftype==_FIELD_TEXT && is_null($fdefault)) {
      			$default = " ";
      		} else if( ( $ftype==_FIELD_INT || $ftype==_FIELD_BIGINT || $ftype==_FIELD_TINYINT )  && is_null($fdefault)){
      			$default =" DEFAULT '0' ";
      		}  else {
      			$default =  !is_null($fdefault)?" DEFAULT '".$this->core->escapeString($fdefault)."' ":" ".($fnull?"NULL":"NOT NULL")." ";
      		}
      		
      		
      		$text .=($k==0?null:" ,")." $fname $fresult ".($default).( in_array($fname,$pkeys)?" PRIMARY KEY ":null ) ." ";
      	}
      	$text .=" ) ";
      	if( $map->droponexist){
      		$this->execute("DROP TABLE IF EXISTS $name");
      	}
      	$this->execute($text);
      	# keys processing
      	foreach( $keys as $k=>$v){
      		$query = null;
      		list($ktype,$kfield) = $v;
      		$keydata = null;      		
      		switch( strtolower($ktype) ){
      			case _KEY_INDEX  : $keydata = "INDEX"; break;
      			case _KEY_UNIQUE : $keydata = "UNIQUE INDEX"; break;
      		}
      		if( !is_null($keydata)){
      			
      			$query = "CREATE $keydata $name"."_"."$kfield ON $name ( ".$kfield." ) ";
      			$this->execute($query);
      		}
      	}
      	$this->execute("COMMENT ON TABLE ".$name." IS 'Created by Gepard, version: ".$this->core->GetKernelVersion()."'");
      	return true;
	}
	
	private function types($type,$size=null){

		$result = null;
        switch ($type)
        {
            case _FIELD_INTAUTO:
                $result = " SERIAL ";
                break;
            case _FIELD_BIGINTAUTO:
                $result = " BIGSERIAL ";
                break;
            case _FIELD_INT:
                $result = " integer ";
                break;
            case _FIELD_BIGINT:
                $result = " BIGINT ";
                break;
            case _FIELD_TINYINT:
                $result = " SMALLINT ";
                break;
            case _FIELD_SMALLINT:
                $result = " SMALLINT ";
                break;
            case _FIELD_CHAR:
                $result = "CHARACTER VARYING(" . ($size != null ? (int) $size : "255") . ") ";
                break;
            case _FIELD_CONSTCHAR:
                $result = "char(" . ($size != null ? (int) $size : "255") . ") ";
                break;
            case _FIELD_DATE:
                $result = "DATE";
                break;
            case _FIELD_DATETIME:
            	$result = "TIMESTAMP";
            	break;
            case _FIELD_TIMESTAMP:
            	$result = "TIMESTAMP";
            	break;
            case _FIELD_TIME:
            	$result = "TIME";
            	break;
            case _FIELD_FLOAT:
            	$result = "FLOAT";
            	break;               
            case _FIELD_REAL:
            	$result = "REAL";
            	break;
            case _FIELD_DOUBLE:
            	$result = " DOUBLE PRECISION";
            	break;
            case _FIELD_DECIMAL:
            	$result = "DECIMAL(".($size != null ? $size : "10,5").") ";
            	break;      	
            case _FIELD_TEXT:
                $result = "TEXT ";
                break;
            case _FIELD_LONGTEXT:
            	$result = "TEXT";
            	break;
            case _FIELD_BIT:
            	$result = "BIT";
            	break;          
            case _FIELD_BOOL:
            	$result = "BOOLEAN";
            	break;
            case _FIELD_BLOB:
            	$result = "BYTEA";
            	break;
            default:
                "character varying(255)";
                break;

        }
        return $result;
	}		
      # checking if sql table exists in current working database
    public function tableexists($table){
      	return in_array($table,$this->gettables());
      }
	
    public function gettables($database=null){
		$sql = "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'";
		$q = pg_query($this->connection,$sql);
		$tables=array();
		while( $row = pg_fetch_row($q)){
			$tables[]=$row[0];
		}
		pg_free_result($q);
		return $tables;
    }
    public function getfields($table){
		# escaping disabled charcters of tablename
		$table = $this->core->escapeString($table);    	
    	$fiels = array();
		# trying to make a query    	
		$result = $this->execute("SELECT * FROM information_schema.columns WHERE table_name ='".$table."'");
		if (pg_num_rows($result) > 0) {
				while ($row = pg_fetch_assoc($result)) {
					$type= $this->type_defback($row["data_type"]);
					$null = ($row["is_nullable"]=="YES")?true:false;
					if( preg_match("/nextval/",$row["column_default"])){
						$type=( $type==_FIELD_BIGINT?_FIELD_INTAUTO:_FIELD_BIGINTAUTO);
						$row["column_default"]=null;
					}
					if( preg_match("/^(.+)\:\:(.+)$/",$row["column_default"],$tmp)){
						$row["column_default"] = $tmp[1];
					}
					if( preg_match("/^'(.+)'$/",trim($row["column_default"]),$tmp)){
						$row["column_default"]=$tmp[1];
					}
					if( $type==_FIELD_DECIMAL){
						$size = $row["numeric_precision"].",".$row["numeric_scale"];
					} else $size = $row["character_maximum_length"];
					
					$fields[]=array("name"=>$row["column_name"],"type"=>$type,"size"=>$size,"default"=>$row["column_default"],"null"=>$null);
			}
		}
		return $fields;
    }
    public function getdatabases(){
    	# query for list of databases
    	$dblist = $this->execute("SELECT datname FROM pg_database");
    	$list = array();
		while( $row = pg_fetch_row($dblist)){
			$list[] = $row[0];
		}
    	return $list;
    }
    # getting current database
    public function getdatabase(){
    	return $this->Database;
    }
    
    # converting for database backup
    private function type_defback($type){
        $result = null;
        switch ($type)
        {
        	case "smallint":
        		$result = _FIELD_SMALLINT;
        		break;
            case "integer":
                $result = _FIELD_INT;
                break;
            case "bigint":
                $result = _FIELD_BIGINT;
                break;
            case "character":
            	$result = _FIELD_CONSTCHAR;
            	break;
            case "character varying":
                $result = _FIELD_CHAR;
                break;
            case "date":
                $result = _FIELD_DATE;
                break;
            case "timestamp without time zone":
                $result = _FIELD_TIMESTAMP;
                break;
            case "time":
            case "time without time zone":
                $result = _FIELD_TIME;
                break;
            case "real":
            	$result = _FIELD_REAL;
            	break;
            case "numeric":
            	$result = _FIELD_DECIMAL;
            	break;
            case "double precision":
            	$result = _FIELD_DOUBLE;
            	break;
            case "boolean":
            	$result = _FIELD_BOOL;
            	break;
            case "bytea":
            	$result = _FIELD_BLOB;
            	break;
            case "bit":
            	$result = _FIELD_BIT;
            	break;
            case "text":
                $result = _FIELD_TEXT;
                break;
            default:
                $result=$type;
                break;
        }
        return $result;
      }   
    


	}
?>